package com.site.dao.impl;

import com.site.dao.ReceiveDao;
import com.site.pojo.Employee;
import com.site.pojo.Receive;
import com.site.utils.JDBCUtils;
import com.site.vo.ReceiveVO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 入库表Dao层实现类
 */
public class ReceiveDaoImpl implements ReceiveDao {
    //查询所有
    @Override
    public List<Receive> selectAll() {
        String sql = "select * from receive";
        Connection conn = null;
        ResultSet rs = null;
        List<Receive> list = new ArrayList<Receive>();
        try{
            conn = JDBCUtils.getConnection();
            rs = JDBCUtils.executeQuery(conn,sql);
            while (rs.next()){
                Receive receive = new Receive();
                receive.setRid(rs.getLong("rid"));
                receive.setGoodsId(rs.getLong("goods_id"));
                receive.setEid(rs.getLong("eid"));
                receive.setReceiveNumber(rs.getString("receive_number"));
                receive.setReceiveCount(rs.getInt("receive_count"));
                receive.setOprTime(rs.getTimestamp("opr_time"));
                list.add(receive);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,null,conn);
        }
        return list;
    }
    //多表：查询入库单及操作人员
    @Override
    public ReceiveVO selectByid(Long id, Long eid) {
        String sql= "select r.receive_number,e.ename,e.sex,e.nationality,e.phone," +
                "e.birthday,e.address,e.employ_date " +
                "from receive r,employee e " +
                "where r.eid=e.eid " +
                "and r.rid=? " +
                "and r.eid=?";
        Connection conn = null;
        ResultSet rs = null;
        ReceiveVO receiveVO = new ReceiveVO();
        try{
            conn = JDBCUtils.getConnection();
            rs = JDBCUtils.executeQuery(conn,sql,id,eid);
            while (rs.next()){
                receiveVO.setReceiveNumber(rs.getString("r.receive_number"));
                Employee employee = new Employee();
                employee.setEname(rs.getString("e.ename"));
                employee.setSex(rs.getString("e.sex"));
                employee.setNationality(rs.getString("e.nationality"));
                employee.setPhone(rs.getString("e.phone"));
                employee.setBirthday(rs.getTimestamp("e.birthday"));
                employee.setAddress(rs.getString("e.address"));
                employee.setEmployDate(rs.getTimestamp("e.employ_date"));
                receiveVO.setEmployee(employee);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,null,conn);
        }
        return receiveVO;
    }

    ////按id查询
    @Override
    public Receive selectByid(Long id) {
        String sql = "select * from receive where rid=?";
        Connection conn = null;
        ResultSet rs = null;
        Receive receive = new Receive();
        try{
            conn = JDBCUtils.getConnection();
            rs = JDBCUtils.executeQuery(conn,sql,id);
            while (rs.next()){
                receive.setRid(rs.getLong("rid"));
                receive.setGoodsId(rs.getLong("goods_id"));
                receive.setEid(rs.getLong("eid"));
                receive.setReceiveNumber(rs.getString("receive_number"));
                receive.setReceiveCount(rs.getInt("receive_count"));
                receive.setOprTime(rs.getTimestamp("opr_time"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,null,conn);
        }
        return receive;
    }

    //按入库编号查询
    @Override
    public Receive selectByReceiveNumber(String receiveNumber) {
        String sql = "select * from receive where receive_number=?";
        Connection conn = null;
        ResultSet rs = null;
        Receive receive = new Receive();
        try{
            conn = JDBCUtils.getConnection();
            rs = JDBCUtils.executeQuery(conn,sql,receiveNumber);
            while (rs.next()){
                receive.setRid(rs.getLong("rid"));
                receive.setGoodsId(rs.getLong("goods_id"));
                receive.setEid(rs.getLong("eid"));
                receive.setReceiveNumber(rs.getString("receive_number"));
                receive.setReceiveCount(rs.getInt("receive_count"));
                receive.setOprTime(rs.getTimestamp("opr_time"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,null,conn);
        }
        return receive;
    }

    //新增入库单
    @Override
    public int addReceive(Receive receive) {
        int num = 0;
        String sql = "insert into receive(goods_id,eid,receive_number,receive_count,opr_time) values(?,?,?,?,?)";
        Object[] param = {receive.getGoodsId(),receive.getEid(),receive.getReceiveNumber(),receive.getReceiveCount(),receive.getOprTime()};
        try{
            num = JDBCUtils.executeUpdate(sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    //通过id删除入库单
    @Override
    public int delReceiveByid(Long id) {
        int num = 0;
        String sql = "delete from receive where rid=?";
        Object[] param = {id};
        try{
            num = JDBCUtils.executeUpdate(sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    //通过入库编号删除入库单
    @Override
    public int delReceiveByReceiveNumber(String receiveNumber) {
        int num = 0;
        String sql = "delete from receive where rid=?";
        Object[] param = {receiveNumber};
        try{
            num = JDBCUtils.executeUpdate(sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }

    //通过receive.id修改入库单
    @Override
    public int updateReceive(Receive receive) {
        int num = 0;
        String sql = "update receive set goods_id=?,eid=?,receive_number=?,receive_count=?,opr_time=? where rid=?";
        Object[] param = {receive.getGoodsId(),receive.getEid(),receive.getReceiveNumber(),receive.getReceiveCount(),receive.getOprTime()
        ,receive.getRid()};
        try{
            num = JDBCUtils.executeUpdate(sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }
    //[事务：]
    //新增入库单
    @Override
    public int addReceive(Connection conn, Receive receive) {
        int num = 0;
        String sql = "insert into receive(goods_id,eid,receive_number,receive_count,opr_time) values(?,?,?,?,?)";
        Object[] param = {receive.getGoodsId(),receive.getEid(),receive.getReceiveNumber(),receive.getReceiveCount(),receive.getOprTime()};
        try{
            num = JDBCUtils.executeUpdate(conn,sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }
    //通过id删除入库单
    @Override
    public int delReceiveByid(Connection conn, Long id) {
        int num = 0;
        String sql = "delete from receive where rid=?";
        Object[] param = {id};
        try{
            num = JDBCUtils.executeUpdate(conn,sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }
    //通过入库编号删除入库单
    @Override
    public int delReceiveByReceiveNumber(Connection conn, String receiveNumber) {
        int num = 0;
        String sql = "delete from receive where rid=?";
        Object[] param = {receiveNumber};
        try{
            num = JDBCUtils.executeUpdate(conn,sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }
    //通过receive.id修改入库单
    @Override
    public int updateReceive(Connection conn, Receive receive) {
        int num = 0;
        String sql = "update receive set goods_id=?,eid=?,receive_number=?,receive_count=?,opr_time=? where rid=?";
        Object[] param = {receive.getGoodsId(),receive.getEid(),receive.getReceiveNumber(),receive.getReceiveCount(),receive.getOprTime()
                ,receive.getRid()};
        try{
            num = JDBCUtils.executeUpdate(conn,sql,param);
        }catch (Exception e){
            e.printStackTrace();
        }
        return num;
    }
}
